<?php

define('T_FRIENDMESSAGE', 0);
define('T_FRIENDINVITE',  1);
define('T_SYSTEMMESSAGE', 2);

class messageDao
{
    private $m_db;

    public function __construct($strHost, $strUserName, $strPassWord, $strDatabase)
    {
        $this->m_db = new mysqli($strHost, $strUserName, $strPassWord, $strDatabase);
        if (mysqli_connect_errno())
        {
            throw 'Error: Could not connect to database. Please try again later.';
        }
    }

    public function __destruct()
    {
        $this->m_db->close();
    }

    public function messageTopList($intTopN)
    {
        $FMT_TOPLIST_SQL = 'SELECT top_mesg_id, top_mesg_title, insert_time FROM message_topshow LIMIT %u';
        $strSQL = sprintf($FMT_TOPLIST_SQL, $intTopN);
        return $this->getList($strSQL);
    }

    public function messageTopContent($intTopMesgId)
    {
        $FMT_TOPCONTENT_SQL = 'SELECT top_mesg_title, insert_time, top_mesg_content FROM message_topshow '.
            'WHERE top_mesg_id = %u';
        $strSQL = sprintf($FMT_TOPCONTENT_SQL, $intTopMesgId);
        return $this->getSingleRow($strSQL);
    }

    public function addTopMessage($strMesgTitle, $strMesgContent)
    {
        if (0 == strlen($strMesgTitle) || 0 == strlen($strMesgContent))
        {
            return false;
        }
        $intTime = time();
        $FMT_INSERT_TOPMESSAGE_SQL = 'INSERT INTO message_topshow '.
            '(top_mesg_title, insert_time, top_mesg_content) '.
            'VALUES("%s", %u, "%s")';
        $strSQL = sprintf($FMT_INSERT_TOPMESSAGE_SQL, $strMesgTitle, $intTime, $strMesgContent);
        return $this->Update($strSQL);
    }

    public function delTopMessage($intTopMesgId)
    {
        $intTime = time();
        $FMT_DELETE_TOPMESSAGE_SQL = 'DELETE FROM message_topshow WHERE top_mesg_id = %u';
        $strSQL = sprintf($FMT_DELETE_TOPMESSAGE_SQL, $intTopMesgId);
        return $this->Update($strSQL);
    }

    public function messageList($intUserId, $intTypeId)
    {
        // 根据$user_id和$type_id从收件箱里拉取列表
        // recv_box表按照$user_id和$mesg_id做主键，innodb
        $FMT_LIST_SQL = 'SELECT mesg_id, hasread, send_user_id, send_user_name, '.
            'mesg_title, recv_time FROM message_recvbox WHERE recv_user_id = %u AND type_id = %u';
        $strSQL = sprintf($FMT_LIST_SQL, $intUserId, $intTypeId);
        return $this->getList($strSQL);
    }

    public function messageCount($intUserId)
    {
        // 返回各个类型的总数和未读计数
        $FMT_COUNT_SQL = 'SELECT '.
            'friendinvite_all, friendinvite_unread, '.
            'friendmessage_all, friendmessage_unread, '.
            'systemmessage_all, systemmessage_unread '.
            'FROM message_count WHERE recv_user_id = %u';
        $strSQL = sprintf($FMT_COUNT_SQL, $intUserId);
        return $this->getSingleRow($strSQL);
    }

    public function messageContent($intUserId, $intMesgId)
    {
        // 根据$user_id选择表，然后使用$mesg_id来读取消息明细
        $FMT_CONTENT_SQL = 'SELECT mesg_id, recv_user_id, type_id, send_user_id, send_user_name, '.
            'mesg_title, mesg_content FROM message_content WHERE mesg_id = %u';
        $strSQL = sprintf($FMT_CONTENT_SQL, $intMesgId);
//        echo $strSQL.'\n';
        return $this->getSingleRow($strSQL);
    }

    public function sendMessage($arrMesgInfo)
    {
        if (   !array_key_exists("intRecvUid",     $arrMesgInfo)
            || !array_key_exists("strRecvUname",   $arrMesgInfo)
            || !array_key_exists("intTypeId",      $arrMesgInfo)
            || !array_key_exists("intSendUid",     $arrMesgInfo)
            || !array_key_exists("strSendUname",   $arrMesgInfo)
            || !array_key_exists("strMesgTitle",   $arrMesgInfo)
            || !array_key_exists("strMesgContent", $arrMesgInfo)
           )
        {
            return false;
        }
        $intTime = time();
        // 向message_content插入一条数据
        $FMT_INSERT_CONTENT_SQL = 'INSERT INTO message_content (recv_user_id, type_id, '.
            'send_user_id, send_user_name, recv_time, mesg_title, mesg_content) '.
            'VALUES(%u, %u, %u, "%s", %u, "%s", "%s")';
        $strSQL = sprintf($FMT_INSERT_CONTENT_SQL, $arrMesgInfo['intRecvUid'], $arrMesgInfo['intTypeId'],
            $arrMesgInfo['intSendUid'], $arrMesgInfo['strSendUname'], $intTime,
            $arrMesgInfo['strMesgTitle'], $arrMesgInfo['strMesgContent']);
//        echo $strSQL.'\n';
//        var_dump($arrMesgInfo);
        $this->Update($strSQL);
        $arrMesgInfo['intMesgID'] = mysqli_insert_id($this->m_db);
        assert(0 < $arrMesgInfo['intMesgID']);
        // 向send_box插入一条数据
        $FMT_INSERT_SENDBOX_SQL = 'INSERT INTO message_sendbox (send_user_id, mesg_id, '.
            'type_id, recv_user_id, recv_user_name, send_time, mesg_title) '.
            'VALUES (%u, %u, %u, %u, "%s", %u, "%s");';
        $strSQL = sprintf($FMT_INSERT_SENDBOX_SQL, $arrMesgInfo['intSendUid'], $arrMesgInfo['intMesgID'],
            $arrMesgInfo['intTypeId'], $arrMesgInfo['intRecvUid'], $arrMesgInfo['strRecvUname'],
            $intTime, $arrMesgInfo['strMesgTitle']);
        $this->Update($strSQL);
        // 向recv_box插入一条数据
        $FMT_INSERT_RECVBOX_SQL = 'INSERT INTO message_recvbox (send_user_id, mesg_id, '.
            'type_id, recv_user_id, send_user_name, recv_time, mesg_title) '.
            'VALUES (%u, %u, %u, %u, "%s", %u, "%s");';
        $strSQL = sprintf($FMT_INSERT_RECVBOX_SQL, $arrMesgInfo['intSendUid'], $arrMesgInfo['intMesgID'],
            $arrMesgInfo['intTypeId'], $arrMesgInfo['intRecvUid'], $arrMesgInfo['strSendUname'],
            $intTime, $arrMesgInfo['strMesgTitle']);
        $this->Update($strSQL);
        // 向message_count表中增加未读计数
        $friendinvite_inc  = 0;
        $friendinvite_inc  = 0;
        $systemmessage_inc = 0;

        switch ($arrMesgInfo['intTypeId'])
        {
            case T_SYSTEMMESSAGE:
                $systemmessage_inc = 1;
                break;
            case T_FRIENDINVITE:
                $friendinvite_inc = 1;
                break;
            case T_FRIENDMESSAGE:
                $friendmessage_inc = 1;
                break;
            default:
//                echo 'error type '.$arrMesgInfo['intTypeId'].'\n';
                return false;
        }

        $FMT_INC_COUNT_SQL = 'INSERT INTO message_count (recv_user_id, '.
            'friendmessage_all, friendmessage_unread, '.
            'friendinvite_all, friendinvite_unread, '.
            'systemmessage_all, systemmessage_unread) '.
            'VALUES (%u, %u, %u, %u, %u, %u, %u) '.
            'ON DUPLICATE KEY UPDATE '.
            'friendmessage_all = friendmessage_all + %u, '.
            'friendmessage_unread = friendmessage_unread + %u, '.
            'friendinvite_all = friendinvite_all + %u, '.
            'friendinvite_unread = friendinvite_unread + %u, '.
            'systemmessage_all = systemmessage_all + %u, '.
            'systemmessage_unread = systemmessage_unread + %u';
        $strSQL = sprintf($FMT_INC_COUNT_SQL,
            $arrMesgInfo['intRecvUid'],
            $friendmessage_inc, $friendmessage_inc,
            $friendinvite_inc,  $friendinvite_inc,
            $systemmessage_inc, $systemmessage_inc,
            $friendmessage_inc, $friendmessage_inc,
            $friendinvite_inc,  $friendinvite_inc,
            $systemmessage_inc, $systemmessage_inc);
        $this->Update($strSQL);
    }

    public function setMessageReaded($intUserId, $intMesgId)
    {
        // 读取intMesgId的type和读取状态
        $FMT_SELECT_SINGLE_MESSAGE = 'SELECT hasread, type_id FROM message_recvbox '.
            'WHERE recv_user_id = %u AND mesg_id = %u';
        $strSQL = sprintf($FMT_SELECT_SINGLE_MESSAGE, $intUserId, $intMesgId);
        $readstatus = $this->getSingleRow($strSQL);
        if (isset($readstatus['hasread']) && $readstatus['hasread'] == 0)
        {
            // 设置recv_box设置unread
            $FMT_SET_READED_SQL = 'UPDATE message_recvbox SET hasread = 1 '.
                'WHERE recv_user_id = %u AND mesg_id = %u';
            $strSQL = sprintf($FMT_SET_READED_SQL, $intUserId, $intMesgId);
            $this->Update($strSQL);
            // 修改message_count表
            $friendinvite_dec  = 0;
            $friendinvite_dec  = 0;
            $systemmessage_dec = 0;
            switch ($readstatus['type_id'])
            {
                case T_SYSTEMMESSAGE:
                    $systemmessage_dec = 1;
                    break;
                case T_FRIENDINVITE:
                    $friendinvite_dec  = 1;
                    break;
                case T_FRIENDMESSAGE:
                    $friendmessage_dec = 1;
                    break;
                default:
//                    echo 'error type '.$readstatus['type_id'].'\n';
                    return false;
            }
            $FMT_DEC_COUNT_SQL = 'UPDATE message_count SET '.
                'friendmessage_unread = friendmessage_unread - %u, '.
                'systemmessage_unread = systemmessage_unread - %u, '.
                'friendinvite_unread  = friendinvite_unread  - %u '.
                'WHERE recv_user_id = %u ';
            $strSQL = sprintf($FMT_DEC_COUNT_SQL, $friendmessage_dec, $systemmessage_dec,
                $friendinvite_dec, $intUserId);
            $this->Update($strSQL);
        }
    }

    public function setMessageAllReaded($intUserId, $intTypeId)
    {
        // 设置recv_box设置unread
        $FMT_SET_ALLREADED_SQL = 'UPDATE message_recvbox SET hasread = 1 '.
            'WHERE recv_user_id = %u AND type_id = %u';
        $strSQL = sprintf($FMT_SET_ALLREADED_SQL, $intRecvUid, $intTypeId);
        $this->Update($strSQL);
        // 修改message_count表
        $FMT_SET_COUNT_SQL = '';
        switch ($intTypeId)
        {
            case T_SYSTEMMESSAGE:
                $FMT_SET_COUNT_SQL = 'UPDATE message_count SET systemmessage_unread = 0 '.
                    'WHERE recv_user_id = %u';
                break;
            case T_FRIENDINVITE:
                $FMT_SET_COUNT_SQL = 'UPDATE message_count SET friendinvite_unread = 0 '.
                    'WHERE recv_user_id = %u';
                break;
            case T_FRIENDMESSAGE:
                $FMT_SET_COUNT_SQL = 'UPDATE message_count SET friendmessage_unread = 0 '.
                    'WHERE recv_user_id = %u';
                break;
            default:
//                echo 'error type '.$intTypeId.'\n';
                return false;
        }

        $strSQL = sprintf($FMT_SET_COUNT_SQL, $intRecvUid);
        $this->Update($strSQL);
    }

    public function deleteRecvMessage($intUserId, $intMesgId)
    {
        // 删除message_recvbox中的$mesg_id
        $FMT_SELECT_SINGLE_MESSAGE = 'SELECT hasread, type_id FROM message_recvbox '.
            'WHERE recv_user_id = %u AND mesg_id = %u';
        $strSQL = sprintf($FMT_SELECT_SINGLE_MESSAGE, $intUserId, $intMesgId);
        $readstatus = $this->getSingleRow($strSQL);

        // 删除消息
        $FMT_DELETE_MESSAGE_LIST_SQL = 'DELETE FROM message_recvbox WHERE recv_user_id = %u AND mesg_id = %u';
        $strSQL = sprintf($FMT_DELETE_MESSAGE_LIST_SQL, $intUserId, $intMesgId);
        $this->Update($strSQL);
        // 删除message_content中的$mesg_id
        $FMT_DELETE_MESSAGE_CONTENT_SQL = 'DELETE FROM message_content WHERE mesg_id = %u';
        $strSQL = sprintf($FMT_DELETE_MESSAGE_CONTENT_SQL, $intMesgId);
        $this->Update($strSQL);
        // 修改message_count的总数
        // 如果删除的消息未读，则修改未读计数
        switch ($readstatus['type_id'])
        {
            case T_SYSTEMMESSAGE:
                $FMT_SET_COUNT_SQL = 'UPDATE message_count SET '.
                    'systemmessage_all = systemmessage_all - 1, '.
                    'systemmessage_unread = systemmessage_unread - %u '.
                    'WHERE recv_user_id = %u';
                break;
            case T_FRIENDINVITE:
                $FMT_SET_COUNT_SQL = 'UPDATE message_count SET '.
                    'friendinvite_all = friendinvite_all - 1, '.
                    'friendinvite_unread = friendinvite_unread - %u '.
                    'WHERE recv_user_id = %u';
                break;
            case T_FRIENDMESSAGE:
                $FMT_SET_COUNT_SQL = 'UPDATE message_count SET '.
                    'friendmessage_all = friendmessage_all - 1, '.
                    'friendmessage_unread = friendmessage_unread - %u '.
                    'WHERE recv_user_id = %u';
                break;
            default:
                return false;
        }
        $strSQL = sprintf($FMT_SET_COUNT_SQL, (1-$readstatus['hasread']), $intUserId);
        $this->Update($strSQL);
    }

    public function deleteSendMessage($intUserId, $intMesgId)
    {
        $FMT_DELETE_MESSAGE_SQL = 'DELETE FROM message_sendbox WHERE send_user_id = %u AND mesg_id = %u';
        $strSQL = sprintf($FMT_DELETE_MESSAGE_SQL, $intUserId, $intMesgId);
        $this->Update($strSQL);
    }

    private function Update($strSQL)
    {
//        $strSQL = $this->m_db->escape_string($strSQL);
        assert ( true == $this->m_db->real_query($strSQL));
//        echo $strSQL."##".mysqli_error($this->m_db)."\n";
    }

    private function getSingleRow($strSQL)
    {
//        echo $strSQL.'\n';
        $result_row = false;
        if (false === $this->m_db->real_query($this->m_db->escape_string($strSQL)))
        {
//            echo mysqli_error($this->m_db).'\n';
            return false;
        }
        else
        {
            if ($result = $this->m_db->use_result())
            {
                $result_row = $result->fetch_assoc();
                $result->close();
            }
        }
        return $result_row;
    }

    private function getList($strSQL)
    {
//        echo $strSQL.'\n';
        $result_list = array();
        if (false === $this->m_db->real_query($this->m_db->escape_string($strSQL)))
        {
//            echo mysqli_error($this->m_db).'\n';
            return false;
        }
        else
        {
            if ($result = $this->m_db->use_result()) {
                while ($row = $result->fetch_assoc()) {
                    $result_list[] = $row;
                }
                $result->close();
            }
        }
        return $result_list;
    }

};
?>
